package com.sec.etech.form.cust.service;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.lang.tree.Tree;
import cn.hutool.core.lang.tree.TreeNode;
import cn.hutool.core.lang.tree.TreeUtil;
import cn.hutool.core.util.NumberUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.sec.etech.form.cust.util.SqlUtil;
import com.sec.etech.form.cust.util.ViewUtil;
import com.sec.etech.sys.cal.service.WorkCalService;
import com.sec.etech.sys.service.EtechDictServiceImpl;
import com.sec.etech.sys.util.ContextUtil;
import com.sec.etech.sys.util.DbUtil;
import com.sec.etech.sys.util.RequestHolder;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.write.Number;
import jxl.write.*;
import org.apache.commons.lang3.StringUtils;
import org.openbpm.base.api.response.impl.ResultMsg;
import org.openbpm.base.core.util.BeanUtils;
import org.openbpm.base.core.util.ExceptionUtil;
import org.openbpm.base.core.util.StringUtil;
import org.openbpm.base.rest.util.RequestUtil;
import org.openbpm.bpm.core.manager.BpmTaskManager;
import org.openbpm.bpm.core.model.BpmTask;
import org.openbpm.form.manager.FormCustSqlFieldsManager;
import org.openbpm.form.manager.FormCustSqlViewManager;
import org.openbpm.form.manager.impl.FormCustSqlViewManagerImpl;
import org.openbpm.form.model.custsql.FormCustSql;
import org.openbpm.form.model.custsql.FormCustSqlFields;
import org.openbpm.form.request.SqlViewRequest;
import org.openbpm.form.util.*;
import org.openbpm.form.vo.*;
import org.openbpm.sys.api.freemark.IFreemarkerEngine;
import org.openbpm.sys.api.groovy.IGroovyScriptEngine;
import org.openbpm.sys.api.model.ISysTreeNode;
import org.openbpm.sys.api.model.dto.DataDictDTO;
import org.openbpm.sys.api.service.ISysTreeNodeService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.AsyncResult;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.lang.Boolean;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.concurrent.Future;
import java.util.stream.Collectors;

@Service("etechFormCustSqlViewManager")
public class EtechFormCustSqlViewManager extends FormCustSqlViewManagerImpl
        implements FormCustSqlViewManager {

    protected Logger logger = LoggerFactory.getLogger(getClass());


    @Autowired
    EtechFormCustSqlManager formCustSqlManager;

    @Autowired
    ISysTreeNodeService sysTreeNodeService;


    @Resource
    FormCustSqlFieldsManager formCustSqlFieldsManager;


    @Autowired
    IFreemarkerEngine freemarkerEngine;


    @Resource(name = "etechDictService")
    EtechDictServiceImpl dataDictService;


    @Resource(name = "etechBpmFlowExtHelper")
    EtechBpmFlowExtHelperCust bpmFlowExtHelper;


    @Autowired
    BpmTaskManager bpmTaskManager;

    @Autowired
    IGroovyScriptEngine groovyScriptEngine;


    public ResultMsg<List<SqlButtonVO>> getVoButtons(String code) {
        List<SqlButtonVO> buttonList = null;
        if (StringUtil.isEmpty(code)) {
            return ResultMsg.ERROR("ID不能为空");
        }
        FormCustSql formCustSql = this.formCustSqlManager.getByCode(code, true);
        if (formCustSql == null) {
            return ResultMsg.ERROR("自定义列表不存在,code:" + code);
        }

        if (0 == formCustSql.getStatus().intValue()) {
            return ResultMsg.ERROR("自定义列表已被禁用");
        }

        boolean needAddExport = "1".equals(formCustSql.getCanExport() != null ? formCustSql.getCanExport().toString() : "0");
        // 不自动添加导出，如需要再添加

        return ResultMsg.SUCCESS(buttonList);
    }



    public ResultMsg<FormCustSql> getVo(String code) {
        if (StringUtil.isEmpty(code)) {
            return ResultMsg.ERROR("ID不能为空");
        }

        FormCustSql formCustSql = this.formCustSqlManager.getByCode(code, true);
        if (formCustSql == null) {
            return ResultMsg.ERROR("自定义列表不存在,code:" + code);
        }

        if (0 == formCustSql.getStatus().intValue()) {
            return ResultMsg.ERROR("自定义列表已被禁用");
        }


        formCustSql.setSqlContent(null);
        formCustSql.setCreateBy(null);
        formCustSql.setCreateTime(null);
        formCustSql.setCreator(null);
        formCustSql.setUpdateBy(null);
        formCustSql.setUpdateTime(null);
        formCustSql.setUpdator(null);
        formCustSql.setTableName(null);
        formCustSql.setDsKey(null);

        // 可考虑后端按钮权限检查
        boolean canExport = "1".equals(formCustSql.getCanExport() != null ? formCustSql.getCanExport().toString() : "0");
        formCustSqlManager.checkBtnExport(code, formCustSql, canExport);

        List<FormCustSqlFields> fieldsList = formCustSql.getFieldsList();

        // 显示条件过滤
        Map<String, Object> groovyParam = new HashMap();
        groovyParam.put("formCustSql", formCustSql);
        for (FormCustSqlFields fieldSetting : fieldsList) {
            String showCondition = JSONObjectUtil.getValByKey("showCondition", fieldSetting.getExpand());
            if (StringUtil.isNotEmpty(showCondition)) {
                try {
                    groovyParam.put("fieldSetting", fieldSetting);
                    boolean show = groovyScriptEngine.executeBoolean(showCondition, groovyParam);
                    if (!show) {
                        fieldSetting.setHidden(1);
                    }
                } catch (Exception e) {
                    fieldSetting.setHidden(1);
                }
            }
        }

        // dicMap
        Map<String, Object> dicMap = new HashMap();

        List<SqlConditionVO> conditionList = JSONObjectUtil.toList(formCustSql.getConditions(), SqlConditionVO.class);
        if (!CollectionUtils.isEmpty(conditionList)) {
            Iterator var7 = conditionList.iterator();

            while (var7.hasNext()) {
                SqlConditionVO sqlConditionVO = (SqlConditionVO) var7.next();
                this.getDicMap(dicMap, sqlConditionVO.getCtltype(), sqlConditionVO.getJson());
            }
        }

        if (!CollectionUtils.isEmpty(fieldsList)) {
            Iterator var7 = fieldsList.iterator();
            while (var7.hasNext()) {
                FormCustSqlFields formCustSqlFields = (FormCustSqlFields) var7.next();
                Integer controlType = formCustSqlFields.getControlType();
                String format = formCustSqlFields.getDateFormat();
                this.getDicMap(dicMap, controlType, format);
            }
        }
        // left tree dic map
        try{
            this.getLeftTreeDicMap(formCustSql, dicMap);
        }catch (Exception e){
            logger.warn(e.getMessage());
        }
        formCustSql.setDicMap(dicMap);

        return ResultMsg.SUCCESS(formCustSql);
    }



    private void getLeftTreeDicMap(FormCustSql formCustSql, Map<String, Object> dicMap) {
        String expand = formCustSql.getExpand();
        String treeDataType = JSONObjectUtil.getValByKey("tree.dataType", expand);
        String treeDateFormat = JSONObjectUtil.getValByKey("tree.dateFormat", expand);
        if (StringUtil.isEmpty(treeDataType) || StringUtil.isEmpty(treeDateFormat)) {
            return;
        }

        if (treeDataType.equals("2")) {
            this.getDicMap(dicMap, 5, treeDateFormat);
        } else if (treeDataType.equals("3")) {
            List<? extends ISysTreeNode> treeNodeList = this.sysTreeNodeService.getTreeNodesByType(treeDateFormat);
            if (!CollUtil.isEmpty(treeNodeList)) {
                JSONObject dicJo = new JSONObject();
                dicMap.put(treeDateFormat, dicJo);
                if (!CollectionUtils.isEmpty(treeNodeList)) {
                    Iterator var21 = treeNodeList.iterator();

                    while (var21.hasNext()) {
                        ISysTreeNode iSysTreeNode = (ISysTreeNode) var21.next();
                        dicJo.put(iSysTreeNode.getKey(), iSysTreeNode.getName());
                    }
                }
            }
        } else if (treeDataType.equals("4")) {
            SqlViewRequest request = new SqlViewRequest();
            request.setPage(1);
            request.setPageSize(2147483647);
            request.setCode(treeDateFormat);
            ResultMsg<SqlViewDataVO> listData = this.listData(request);
            if (listData.getIsOk()) {
                JSONArray list = (JSONArray) listData.getData().getList();
                String custSqlId = JSONObjectUtil.getValByKey("tree.custSqlId", expand);
                String custSqlPid = JSONObjectUtil.getValByKey("tree.custSqlPid", expand);
                String custSqlTitle = JSONObjectUtil.getValByKey("tree.custSqlTitle", expand);

                // 先转树过滤掉不匹配的数据，并增加指定TOP的功能
                String rootId = JSONObjectUtil.getValByKey("tree.rootId", expand);

                // 构建node列表
                List<TreeNode<String>> nodeList = CollUtil.newArrayList();

                JSONArray dicList = new JSONArray();
                Iterator var16 = list.iterator();
                while (var16.hasNext()) {
                    Object object = var16.next();
                    JSONObject jObject = (JSONObject) object;

                    String id = jObject.getString(custSqlId);
                    String parentId = jObject.getString(custSqlPid);
                    String name = jObject.getString(custSqlTitle);
                    Long weight = 0l;
                    try{
                        weight = Long.parseLong(jObject.getString("orderby"));
                    }catch (Exception e){
                    }
                    nodeList.add(new TreeNode<>(id, parentId, name, weight));
                }
                // 构建根节点
                List<Tree<String>> treeList = TreeUtil.build(nodeList, rootId);
                tree2List(treeList, dicList);
                dicMap.put("custsql_" + treeDateFormat, dicList);
            }
        }
    }

    private void tree2List(List<Tree<String>> treeList, JSONArray dicList){
        for(Tree<String> tree: treeList){
            JSONObject dicJo = new JSONObject();
            dicJo.put("key", tree.getId());
            dicJo.put("pid", tree.getParentId());
            dicJo.put("title", tree.getName());
            dicList.add(dicJo);
            if(tree.getChildren()!=null){
                tree2List(tree.getChildren(), dicList);
            }
        }
    }


    private void getDicMap(Map<String, Object> dicMap, Integer controlType, String format) {
        // dict 5,7,9,18 （不包含由后台提供的代码表）
        if (Util.list(new Integer[]{5, 7, 9}).contains(controlType) && !dicMap.containsKey(format)) {
            List<DataDictDTO> dictNodeList = this.dataDictService.getDictNodeList4grid(format);
            JSONObject dicJo = new JSONObject(true);
            dicMap.put(format, dicJo);
            if (!CollectionUtils.isEmpty(dictNodeList)) {
                Iterator var6 = dictNodeList.iterator();
                while (var6.hasNext()) {
                    DataDictDTO dic = (DataDictDTO) var6.next();
                    dicJo.put(dic.getKey(), dic.getName());
                }
            }
        }
    }

    // 多实例会签节点有问题
    public ResultMsg<SqlViewDataVO> listData(SqlViewRequest request) {
        long t0 = System.currentTimeMillis();
        long cost = 0;  //seconds

        // 1 准备SQL
        String code = request.getCode();

        Integer curPage = Integer.valueOf(request.getCurPage());
        Integer pageSize = request.getPageSize();

        ResultMsg<SqlViewVO> listDataSql = listDataSql(request);
        SqlViewVO sqlViewVO = listDataSql.getData();


        String queryCountSql = sqlViewVO.getQueryCountSql();
        String queryDataSql = sqlViewVO.getQueryDataSql();
        String querySumSql = sqlViewVO.getQuerySumSql();

        // 检查准备SQL的时间
        cost =  (System.currentTimeMillis() - t0) / 1000;
        if(cost>=1){
            logger.warn("listDataSql, code={}, cost={}秒", code, cost);
        }

        // 2. 查询主记录
        String dsKey = sqlViewVO.getDsKey();
        JdbcTemplate jdbcTemplate = JdbcTemplateUtil.getJdbcTemplate(dsKey);

        // 检查关联流程配置 flow, task
        String bpmExt = sqlViewVO.getBpmExt();

        // __DEL 检查是否需要提前查询任务列表(不推荐，可能被删除)
        List<Map<String, Object>> tasks = null;
        if ("bpm_task_ext".equalsIgnoreCase(bpmExt)) {
            tasks = bpmFlowExtHelper.queryTaskList(sqlViewVO);
            Set<String> setBizId = new HashSet<>();
            for (Map<String, Object> row : tasks) {
                setBizId.add((String) row.get("biz_id_"));
            }
            String bizIdsInTask = "";
            if (!setBizId.isEmpty()) {
                bizIdsInTask = StringUtil.join(setBizId, "','");
            }
            queryDataSql = queryDataSql.replaceAll("bizIdsInTask", bizIdsInTask);
            queryCountSql = queryCountSql.replaceAll("bizIdsInTask", bizIdsInTask);
        }
        // __DEL

        long t1 = System.currentTimeMillis();

        List<Map<String, Object>> queryForList = DbUtil.getJdbcTemplate().queryForList(queryDataSql);

        cost =  (System.currentTimeMillis() - t1) / 1000;
        if(cost>=1){
            logger.warn("执行queryDataSql, code={}, cost={}秒 \r\n sql={}", code, cost, queryDataSql);
        }

        // 3. 数据处理
        // 3.1 后台代码表处理
        handleDataWithDict(queryForList, sqlViewVO);

        // 3.2 检查流程或任务处理
        if ("bpm_task_ext".equalsIgnoreCase(bpmExt)) {
            queryForList = bpmFlowExtHelper.extTaskData(tasks, queryForList, sqlViewVO);
        } else if ("bpm_flow_ext".equalsIgnoreCase(bpmExt)) {
            queryForList = bpmFlowExtHelper.extFlowData(queryForList, sqlViewVO);
        }
        if (bpmExt != null && (bpmExt.startsWith("todotask_") ||
                bpmExt.startsWith("dealtask_"))) {
            queryForList = bpmFlowExtHelper.extTaskData(queryForList, sqlViewVO, bpmExt);
        }

        // 3.3 转换json以及数据后处理
        String list = ViewUtil.changeQueryListToJSONStr(queryForList);
        Object listData = ViewUtil.handleData(code, list, jdbcTemplate, sqlViewVO);

        // 4. 结果数据准备，包含查询总数以及sum统计字段
        // 4.0 结果准备
        SqlViewDataVO sqlViewDataVO = new SqlViewDataVO();
        sqlViewDataVO.setList(listData);


        // 4.1 分页检查
        int count = 0;
        if (queryForList.size() < pageSize.intValue()) {
            count = (curPage.intValue() - 1) * pageSize.intValue() + queryForList.size();
        }

        // 4.2 sum检查及执行
        if (StringUtils.isNotEmpty(querySumSql)) {
            long t2 = System.currentTimeMillis();
            try{
                Map<String, Object> queryForMap = jdbcTemplate.queryForMap(querySumSql);
                sqlViewDataVO.setSumData(queryForMap);
                if(count==0){
                    count = Integer.valueOf(""+queryForMap.get("counts"));
                }
            }catch (Exception e){
                logger.error("querySum:"+e.getMessage()+"\r\nsql="+querySumSql, e);
                //e.printStackTrace();
            }
            cost =  (System.currentTimeMillis() - t2) / 1000;
            if(cost>=1){
                logger.warn("执行querySumSql , code={}, cost={}秒 \r\n sql={}", code, cost, querySumSql);
            }
        }

        // 4.3 count检查及执行
        if(count==0 && queryForList.size()>0){
            long t3 = System.currentTimeMillis();
            count = ((Integer) jdbcTemplate.queryForObject(queryCountSql, Integer.class)).intValue();
            cost =  (System.currentTimeMillis() - t3) / 1000;
            if(cost>=1){
                logger.warn("执行queryCountSql , code={}, cost={}秒 \r\n sql={}", code, cost, queryCountSql);
            }
        }
        sqlViewDataVO.setCount(Integer.valueOf(count));

        // 检查整个方法执行时间
        cost =  (System.currentTimeMillis() - t0) / 1000;
        if(cost>=2){ //总执行时间2秒
            logger.warn("listData 查询完成, code={}, cost={}秒 \r\n sql={}", code, cost, queryDataSql);
        }

        return ResultMsg.SUCCESS(sqlViewDataVO);
    }

    private void handleDataWithDict(List<Map<String, Object>> queryForList, SqlViewVO sqlViewVO) {
        // 后台字典表和字典表扩展
        Map<String, Map<Object, DataDictDTO>> dicMap = new HashMap(); // Map<fieldKey, Map<value, Dict>>
        List<FormCustSqlFields> commonFieldList = sqlViewVO.getFieldList();
        for (FormCustSqlFields field : commonFieldList) {
            // 18 - 数据字典-扩展
            if (18 == field.getControlType()) {
                String fieldKey = field.getFieldName();
                String dictKey = field.getDateFormat();
                List<DataDictDTO> dicts = this.dataDictService.getDictNodeList4grid(dictKey);   //.getDictNodeList(dictKey, false);

                //List<DataDict> dicts = dataDictManager.getDictNodeList(dictKey, false);
                if (dicts != null && !dicts.isEmpty()) {
                    Map<Object, DataDictDTO> mapDictValue = new HashMap<>();
                    for (DataDictDTO dict : dicts) {
                        mapDictValue.put(dict.getKey(), dict);
                    }
                    dicMap.put(fieldKey, mapDictValue);
                }
            }
        }
        // 后台代码表处理
        for (Map rowData : queryForList) {
            for (String filedKey : dicMap.keySet()) {
                if (!rowData.containsKey(filedKey)) {
                    continue;
                }
                Map<Object, DataDictDTO> mapDictValue = dicMap.get(filedKey);
                String value = String.valueOf(rowData.get(filedKey));
                String label = null;
                // 检查是否多个值用逗号分隔
                String[] valueMulti = value.split(",");
                if (valueMulti.length > 1) {
                    //针对用户扩展user_ext特别处理,返回电话号码
                    String mobile = null;
                    for (String value2 : valueMulti) {
                        if (mapDictValue.containsKey(value2)) {
                            if (label == null) {
                                label = mapDictValue.get(value2).getName();
                            } else {
                                label += "," + mapDictValue.get(value2).getName();
                            }
                            if(mapDictValue.get(value2).getExtData()!=null
                            && mapDictValue.get(value2).getExtData().containsKey("mobile")){
                                if(mobile==null){
                                    mobile = mapDictValue.get(value2).getExtData().getString("mobile");
                                }else {
                                    mobile += "," + mapDictValue.get(value2).getExtData().getString("mobile");
                                }
                            }
                        }
                    }
                    // 代码表直接返回 label (_label可以不使用）
                    if (StringUtil.isNotEmpty(label)) {
                        //rowData.put(filedKey, label);
                        rowData.put(filedKey + "_label", label);
                    }
                    if (StringUtil.isNotEmpty(mobile)) {
                        rowData.put(filedKey + "_mobile", mobile);
                    }
                } else {
                    if (mapDictValue.containsKey(value)) {
                        DataDictDTO dictDto = mapDictValue.get(value);
                        // 代码表直接返回 label (_label可以不使用）
                        if (StringUtil.isNotEmpty(dictDto.getName())) {
                            //rowData.put(filedKey,  dictDto.getName());
                            rowData.put(filedKey + "_label", dictDto.getName());
                        }
                        if (dictDto != null && dictDto.getExtData() != null) {
                            rowData.put(filedKey + "_extdata", dictDto.getExtData().clone());
                            if(dictDto.getExtData().containsKey("mobile")){
                                rowData.put(filedKey + "_mobile", dictDto.getExtData().getString("mobile"));
                            }
                        }
                    }
                }
            } //for
        }
        // 检查排序字段是否代码表?
        // 只能实现页内排序，可放到前端处理
    }

    @Cacheable("dashboardTodoCount")
    public Integer getCount4listData(String code, String user) {
        //用户增加缓存时区分不同用户
        return getCount4listData(code);
    }

    public Integer getCount4listData(String code) {

        // copy from listData 和 listDataSql
        // 统计去掉order by

        FormCustSql viewVo = this.formCustSqlManager.getByCode(code, true);
        JSONObject expandJo = JSONObjectUtil.parseExpand(viewVo.getExpand());
        String bpmExt = JSONObjectUtil.getValByKey("bpmExt", expandJo);
        // 流程任务定制逻辑
        if (bpmExt != null && (bpmExt.startsWith("todotask_") ||
                bpmExt.startsWith("dealtask_"))) {
            // TODO 增加判断 原来SQL有流程实例字段 bpminstid或bpm_inst_id
            String bpmInstField = null;
            List<FormCustSqlFields> fields = viewVo.getFieldsList();
            for (FormCustSqlFields field : fields) {
                String fieldName = field.getFieldName().toLowerCase();
                if (fieldName.indexOf("bpminstanceid") >= 0 || fieldName.indexOf("instanceid") >= 0) {
                    bpmInstField = fieldName;
                    break;
                }
            }
            boolean withBizId = (bpmInstField == null);
            // 对性能优化有用，业务上无差别
            String taskSql = EtechTaskSqlHelper.getBpmTaskSqlByExtType(bpmExt, withBizId);
            if (StringUtil.isNotEmpty(taskSql)) {
                String sqlContent = viewVo.getSqlContent();
                // fix 去掉SQL语句的最后的"分号"
                sqlContent = sqlContent.trim();
                if (sqlContent.endsWith(";")) {
                    sqlContent = sqlContent.substring(0, sqlContent.length() - 1);
                }

                if (withBizId) {
                    sqlContent = "select task.*, bus.* " +
                            " from (" + taskSql + " ) task, (" + sqlContent + ") bus" +
                            " where task.biz_id_ = bus." + viewVo.getPkName();
                } else {
                    sqlContent = "select task.*, bus.* " +
                            " from (" + taskSql + " ) task, (" + sqlContent + ") bus" +
                            " where task.inst_id_ = bus." + bpmInstField;
                }
                viewVo.setSqlContent(sqlContent);
            }
        }

        SqlConstructVO sqlConstructVO = ViewUtil.getSqlConstruct(viewVo, null);

        String sql = sqlConstructVO.getSql();

        sql = ViewUtil.replaceParams(sql);

        String queryCountSql = SqlUtil.getCountSql(sql);

        int count = DbUtil.getJdbcTemplate().queryForObject(queryCountSql, Integer.class).intValue();

        return count;
    }

    /**
     * 统计待办任务数量和超期任务数量，只适用于待办任务列表
     *
     * @return
     */
    // @Cacheable("dashboardTodoCount")
    @Async()
    public Future<Map<String, Object>> getCounts4todolistData(Map<String, Object> todoCount, Boolean isGroup, String code, Map<String, Object> contextMap) {
        if (isGroup == null) {
            return new AsyncResult<Map<String, Object>>(todoCount);
        }


        // System.out.println(code);
        // Date startTime = new Date();


        List<String> sqlList = getQueryCountSql(code, contextMap);
        String sql = sqlList.get(0);
        String queryCountSql = sqlList.get(1);

        JdbcTemplate jdbcTemplate = DbUtil.getJdbcTemplate();


        // 所有代办
        // int allCount = jdbcTemplate.queryForObject(queryCountSql, Integer.class).intValue();
        // todoCount.put((isGroup ? "count4group" : "count"), allCount);
        todoCount.put((isGroup ? "count4groupSql" : "countSql"), queryCountSql);

        // if (allCount==0){
        //     todoCount.put((isGroup ? "today4group" : "today"), 0);
        //     todoCount.put((isGroup ? "expired4group" : "expired"), 0);
        //     return new AsyncResult<Map<String, Object>>(todoCount);
        //
        // }

        // Date date2 = new Date();
        // System.out.println("time2 use:" + DateUtil.between(date1, date2, DateUnit.MS));


        // 到期待办
        int todyCount = 0;
        String queryTodaySql = "";
        try {
            // fix t1.plandate in proc_process
            if (sql.toLowerCase().indexOf("o.remain_days_") > 0) {
                queryTodaySql = queryCountSql + " AND task.remain_days_ = 0 ";
            }else if (sql.toLowerCase().indexOf("t1.plandate") > 0) {
                queryTodaySql = queryCountSql + " AND DATEDIFF(t1.plandate,NOW()) = 0 "; // 非 BPM 流程
            } else {
                //queryTodaySql = queryCountSql + " AND task.remain_days_ = 0 ";
            }

            // todyCount = jdbcTemplate.queryForObject(queryTodaySql, Integer.class).intValue();
            // todoCount.put((isGroup ? "today4group" : "today"), todyCount);
            todoCount.put((isGroup ? "today4groupSql" : "todaySql"), queryTodaySql);

        } catch (Exception e) {
            logger.error("获取当日超期任务失败，code =[ " + code + " ] sql=[ " + queryTodaySql + " ] message = " + e.getMessage(), e);

        }

        // Date date3 = new Date();
        // System.out.println("time3 use:" + DateUtil.between(date2, date3, DateUnit.MS));


        // 超期待办
        int expiredCount = 0;
        if (queryCountSql.indexOf("o.remain_days_") > 0) {
            // BPM 流程任务
            // 检查是否有剩余天数，如有增加 剩余天数<0 查询超期任务数量
            try {
                String checkWhereStr = "LEFT JOIN bpm_task_opinion o ON t.id_ = o.task_id_ WHERE";
                int index = queryCountSql.indexOf(checkWhereStr);
                if (index > 0) {
                    index += checkWhereStr.length();
                    String queryCountSql4expired = queryCountSql.substring(0, index) + " o.remain_days_<0 and " + queryCountSql.substring(index);
                    // expiredCount = jdbcTemplate.queryForObject(queryCountSql4expired, Integer.class).intValue();
                    todoCount.put((isGroup ? "expired4groupSql" : "expiredSql"), queryCountSql4expired);

                }
            } catch (Exception e) {
                logger.error("工作流任务 统计超期任务失败：" + e.getMessage(), e);
            }
        } else if (queryCountSql.toLowerCase().indexOf("t1.plandate") > 0) {
            // 非工作流任务  plandate
            try {
                int index = queryCountSql.toLowerCase().indexOf("where", queryCountSql.indexOf("t1.plandate"));
                if (index > 0) {
                    index += index + "where".length();
                    Date today = WorkCalService.beginOfDay(new Date());
                    String queryCountSql4expired = queryCountSql.substring(0, index) + " t1.plandate<? and " + queryCountSql.substring(index);
                    // expiredCount = jdbcTemplate.queryForObject(queryCountSql4expired, new Object[]{today}, Integer.class).intValue();
                    todoCount.put((isGroup ? "expired4groupSql" : "expiredSql"), queryCountSql4expired);


                }
            } catch (Exception e) {
                logger.error("非工作流任务 统计超期任务失败：" + e.getMessage(), e);
            }
        }
        // todoCount.put((isGroup ? "expired4group" : "expired"), expiredCount);


        // Date date4 = new Date();
        // System.out.println("time4 use:" + DateUtil.between(date3, date4, DateUnit.MS));

        // Date endTime = new Date();
        // System.out.println(code + " time use: " + DateUtil.between(startTime, endTime, DateUnit.MS));
        // System.out.println("------------------------------------------");

        return new AsyncResult<Map<String, Object>>(todoCount);

    }

    // copy from listData 和 listDataSql
    // @Cacheable("getQueryCountSql")
    public List<String> getQueryCountSql(String code, Map<String, Object> contextMap) {
        ArrayList<String> sqlList = new ArrayList<>();

        FormCustSql viewVo = this.formCustSqlManager.getByCode(code, true);
        JSONObject expandJo = JSONObjectUtil.parseExpand(viewVo.getExpand());
        String bpmExt = JSONObjectUtil.getValByKey("bpmExt", expandJo);
        // 流程任务定制逻辑
        String bpmInstField = null;
        if (bpmExt != null && (bpmExt.startsWith("todotask_") ||
                bpmExt.startsWith("dealtask_"))) {
            // TODO 增加判断 原来SQL有流程实例字段 bpminstid或bpm_inst_id
            List<FormCustSqlFields> fields = viewVo.getFieldsList();
            for (FormCustSqlFields field : fields) {
                String fieldName = field.getFieldName().toLowerCase();
                if (fieldName.indexOf("bpminstanceid") >= 0 || fieldName.indexOf("instanceid") >= 0) {
                    bpmInstField = fieldName;
                    break;
                }
            }
            boolean withBizId = (bpmInstField == null);
            // 对性能优化有用，业务上无差别
            String taskSql = EtechTaskSqlHelper.getBpmTaskSqlByExtType(bpmExt, withBizId);
            if (StringUtil.isNotEmpty(taskSql)) {
                String sqlContent = viewVo.getSqlContent();
                // fix 去掉SQL语句的最后的"分号"
                sqlContent = sqlContent.trim();
                if (sqlContent.endsWith(";")) {
                    sqlContent = sqlContent.substring(0, sqlContent.length() - 1);
                }

                if (withBizId) {
                    sqlContent = "select task.*, bus.* " +
                            " from (" + taskSql + " ) task, (" + sqlContent + ") bus" +
                            " where task.biz_id_ = bus." + viewVo.getPkName();
                } else {
                    sqlContent = "select task.*, bus.* " +
                            " from (" + taskSql + " ) task, (" + sqlContent + ") bus" +
                            " where task.inst_id_ = bus." + bpmInstField;
                }
                viewVo.setSqlContent(sqlContent);
            }
        }


        SqlConstructVO sqlConstructVO = ViewUtil.getSqlConstruct(viewVo, null);
        String sql = sqlConstructVO.getSql();
        sqlList.add(sql);
        sql = ViewUtil.replaceParams(sql, contextMap);


        String queryCountSql = SqlUtil.getCountSql(sql);


        // 优化SQL，去掉不影响统计数字的部分
        queryCountSql = queryCountSql.replaceAll(", (SELECT GROUP_CONCAT(pl.identity_ SEPARATOR ';') FROM bpm_task_identitylink pl WHERE pl.task_id_ = t.id_ AND pl.type_ = 'user') AS assignee_ids_", "");
        queryCountSql = queryCountSql.replaceAll("ORDER BY o.plan_complete_day_", "");

        // 非 BPM 流程跳过
        if (sql.toLowerCase().indexOf("t1.plandate") == -1) {
            queryCountSql = SqlUtil.getToDoCountSql(queryCountSql, viewVo.getPkName(), bpmInstField);
        }

        sqlList.add(queryCountSql);

        return sqlList;
    }


    /**
     * 解析查询查询，构建SQL
     *
     * @param request
     * @return
     */
    public ResultMsg<SqlViewVO> listDataSql(SqlViewRequest request) {
        String code = request.getCode();
        // 分页数据
        Integer pageNo = Integer.valueOf(request.getCurPage());
        Integer pageSize = request.getPageSize();

        // 查询条件
        String queryData = request.getParams();
        JSONObject queryDataObject = JSONObject.parseObject(queryData);
        // 针对ag-grid参数特别处理
        mergeGirdParams2QueryData(queryDataObject, request);

        SqlQueryDataVO sqlQueryDataVO = JSONObject.toJavaObject(queryDataObject, SqlQueryDataVO.class);

        FormCustSql viewVo = this.formCustSqlManager.getByCode(code, true);
        JSONObject expandJo = JSONObjectUtil.parseExpand(viewVo.getExpand());
        String bpmExt = JSONObjectUtil.getValByKey("bpmExt", expandJo);
        if (StringUtil.isNotEmpty(request.getBpmExt())) {
            bpmExt = request.getBpmExt();
        }
        if (queryDataObject != null){
            if (queryDataObject.containsKey("bpmExt")) {
                if ("todotask_my".equals(queryDataObject.getString("bpmExt")) || "todotask_group".equals(queryDataObject.getString("bpmExt"))) {
                    bpmExt = queryDataObject.getString("bpmExt");
                }
            }
        }


        // 兼容 bpm_task_ext （即将删除）
        if ("bpm_task_ext".equalsIgnoreCase(bpmExt)) {
            String columnBizId = JSONObjectUtil.getValByKey("columnBizId", expandJo);
            // and ${columnBizId} in ('${bizIdsInTask}')
            List<SqlSearchVO> listQueryData = sqlQueryDataVO.getQueryData();
            if (listQueryData == null) {
                listQueryData = new ArrayList<>();
                sqlQueryDataVO.setQueryData(listQueryData);
            }
            SqlSearchVO vo = new SqlSearchVO(columnBizId, "in", "bizIdsInTask");
            vo.setIsSystem(1);
            vo.setIsInit(1);
            listQueryData.add(vo);
        }

        // 流程任务定制逻辑
        if (bpmExt != null && (bpmExt.startsWith("todotask_") ||
                bpmExt.startsWith("dealtask_"))) {
            // TODO 增加判断 原来SQL有流程实例字段 bpminstid或bpm_inst_id
            String bpmInstField = null;
            List<FormCustSqlFields> fields = viewVo.getFieldsList();
            for (FormCustSqlFields field : fields) {
                String fieldName = field.getFieldName().toLowerCase();
                if (fieldName.indexOf("bpminstanceid") >= 0 || fieldName.indexOf("instanceid") >= 0) {
                    bpmInstField = fieldName;
                    break;
                }
            }
            boolean withBizId = (bpmInstField == null);
            // 对性能优化有用，业务上无差别
            String taskSql = EtechTaskSqlHelper.getBpmTaskSqlByExtType(bpmExt, withBizId);
            if (StringUtil.isNotEmpty(taskSql)) {
                String sqlContent = viewVo.getSqlContent();
                // fix 去掉SQL语句的最后的"分号"
                sqlContent = sqlContent.trim();
                if (sqlContent.endsWith(";")) {
                    sqlContent = sqlContent.substring(0, sqlContent.length() - 1);
                }
                // order by a.lastdate desc
                boolean orderByLastdate = false;
                int n1 = sqlContent.toLowerCase().indexOf("order by");
                if (n1 > 0) {
                    int n2 = sqlContent.toLowerCase().indexOf("lastdate", n1);
                    if (n2 > 0) {
                        for (FormCustSqlFields field : fields) {
                            if ("lastdate".equalsIgnoreCase(field.getFieldName())) {
                                orderByLastdate = true;
                                break;
                            }
                        }

                    }
                }
                // TODO 定制工艺计划任务, 需要优化到配置界面
                // 排除 T10.2-T10.7
                // 0525 增加 T10.9
                if (code.indexOf("ComponentTecher") >= 0) {
                    boolean hasWhere = taskSql.toLowerCase().indexOf("where", 400) > 0;
                    String nodeIdCondition = (hasWhere ? " and " : " where ") + " ( t.node_id_ not like 'T10.%' or t.node_id_ like 'T10.1%' or t.node_id_ like 'T10.8%' or t.node_id_ like 'T10.9%') ";
                    boolean hasT2 = taskSql.indexOf("bpm_task t2")>0;
                    nodeIdCondition=(!hasT2 ? nodeIdCondition : " and t2.node_id_ not like 'T10.%' or t2.node_id_ like 'T10.1%' or t2.node_id_ like 'T10.8%' or t2.node_id_ like 'T10.9%' ");
                    int intOrderBy = taskSql.toLowerCase().indexOf("order by");
                    if (intOrderBy > 0) {
                        taskSql = taskSql.substring(0, intOrderBy) + nodeIdCondition + taskSql.substring(intOrderBy);
                    } else {
                        taskSql += nodeIdCondition;
                    }
                }

                if (withBizId) {
                    sqlContent = "select task.*, bus.* " +
                            " from (" + taskSql + " ) task, (" + sqlContent + ") bus" +
                            " where task.biz_id_ = bus." + viewVo.getPkName() + " ORDER BY task.plan_complete_day_";
                } else {
                    sqlContent = "select task.*, bus.* " +
                            " from (" + taskSql + " ) task, (" + sqlContent + ") bus" +
                            " where task.inst_id_ = bus." + bpmInstField + " ORDER BY task.plan_complete_day_";
                }
                if (orderByLastdate) {
                    sqlContent += " order by bus.lastdate desc";
                }
                viewVo.setSqlContent(sqlContent);
            }
        }

        Map<String, String> urlParams = ViewUtil.getUrlParams(queryData);

        if (sqlQueryDataVO != null) {
            sqlQueryDataVO.setParamsData(urlParams);
        }


        List<FormCustSqlFields> fieldList = viewVo.getFieldsList();

        for (int i = fieldList.size()-1; i > -1; i--) {
            FormCustSqlFields field = fieldList.get(i);
            String showCondition = JSONObjectUtil.getValByKey("showCondition", field.getExpand());
            if (StringUtil.isNotEmpty(showCondition)) {
                try {
                    boolean isShow = groovyScriptEngine.executeBoolean(showCondition, new HashMap());
                    if (!isShow) {
                        fieldList.remove(i);
                    }
                } catch (Exception e) {
                    logger.error("删除隐藏列出错！");
                    e.printStackTrace();
                }
            }
        }

        SqlConstructVO sqlConstructVO = ViewUtil.getSqlConstruct(viewVo, sqlQueryDataVO);
        Map<String, Object> paramsMap = sqlConstructVO.getParamsMap();
        JSONObject freemarkerParams = sqlConstructVO.getFreemarkerParams();
        freemarkerParams.putAll(urlParams);

        SqlViewVO sqlViewVO = ViewUtil.getFieldColumnJo(fieldList, expandJo);
        if (StringUtil.isNotEmpty(bpmExt)) {
            sqlViewVO.setBpmExt(bpmExt);

            // 不推荐使用，以下代码可能被移除
            if ("bpm_task_ext".equalsIgnoreCase(bpmExt)) {
                String bpmTaskType = JSONObjectUtil.getValByKey("bpmTaskType", expandJo);
                if (StringUtil.isNotEmpty(request.getBpmExtTaskType())) {
                    bpmTaskType = request.getBpmExtTaskType();
                }
                sqlViewVO.setBpmExtTaskType(bpmTaskType);
            }


            String columnBizId = viewVo.getPkName();
            if (StringUtil.isEmpty(columnBizId)) {
                //兼容旧版本
                String columnBizId2 = JSONObjectUtil.getValByKey("columnBizId", expandJo);
                if (StringUtil.isNotEmpty(columnBizId2)) {
                    columnBizId = columnBizId2;
                }
            }
            sqlViewVO.setBpmExtColumnBizId(columnBizId);
        }

        String realSqlColumn = sqlViewVO.getRealSqlColumn();
        String sumSqlColumn = sqlViewVO.getSumSqlColumn();

        String sql = sqlConstructVO.getSql();
        // fix
        //sql = SqlUtil.fixTwoOrderBy(sql);

        sql = ViewUtil.replaceParams(sql);


        sql = this.freemarkerEngine.parseByString(sql, freemarkerParams);
        sql = this.freemarkerEngine.parseByString(sql, paramsMap);


        String queryCountSql = SqlUtil.getCountSql(sql);
        // TODO （方案待确认）排序字段如为数据字典，考虑将代码表加入到SQL中，仅仅针对定制SQL的代码表
        String queryDataSql = ViewUtil.getQueryDataSql(realSqlColumn, sql, pageNo.intValue(), pageSize.intValue());

        String querySumSql = null;
        if (!StringUtils.isBlank(sumSqlColumn)){
            querySumSql = SqlUtil.getSumSql(sumSqlColumn, sql);
        }

//        String querySumSql = ViewUtil.getQuerySumSql(sumSqlColumn, realSqlColumn, sql);

        sqlViewVO.setFieldList(fieldList);
        sqlViewVO.setQueryCountSql(queryCountSql);
        sqlViewVO.setQueryDataSql(queryDataSql);
        sqlViewVO.setQuerySumSql(querySumSql);
        sqlViewVO.setDsKey(viewVo.getDsKey());
        sqlViewVO.setSqlName(viewVo.getName());
        sqlViewVO.setQueryData(sqlConstructVO.getQueryData());
        sqlViewVO.setParamsMap(paramsMap);

        // log
        String busModule = JSONObjectUtil.getValByKey("busModule", expandJo);
        if (StringUtil.isEmpty(busModule)) {
            formCustSqlManager.checkModuleInFormCustSql(viewVo);
            busModule = JSONObjectUtil.getValByKey("busModule", viewVo.getExpand());
        }
        if (logger.isInfoEnabled()) {
            String ip = RequestUtil.getIpAddr(RequestHolder.getRequest());
            logger.info("invoke custSqlView, code={}, module={}, user={}, ip={}", code, busModule,
                    ContextUtil.getCurrentUser().getAccount(), ip);
        }

        return ResultMsg.SUCCESS(sqlViewVO);
    }

    private void mergeGirdParams2QueryData(JSONObject queryDataObject, SqlViewRequest request) {
        // 针对ag-grid参数特别处理
        if (queryDataObject == null) {
            return;
        }
        JSONObject gridParams = queryDataObject;

        // 检查排序
        String orderBy = "";
        if (gridParams.containsKey("sortModel")) {
            JSONArray sortModel = gridParams.getJSONArray("sortModel");
//            for(int i=0; i<sortModel.size(); i++){
//                JSONObject sortObj = sortModel.getJSONObject(i);
//                orderBy += sortObj.getString("colId")+"#"+sortObj.getString("sort")+",";
//            }
            // 多个排序只取第一个
            if (sortModel != null && sortModel.size() > 0) {
                JSONObject sortObj = sortModel.getJSONObject(0);
                orderBy = sortObj.getString("colId") + "#" + sortObj.getString("sort") + ",";
            }
            gridParams.remove("sortModel");
        }
        if (StringUtil.isNotEmpty(orderBy)) {
            // 不保留原有排序
            String newOrderBy = orderBy; // + queryDataObject.getString("orderBy");
            queryDataObject.put("orderBy", newOrderBy);
        }
        // 检查字段过滤
        // https://www.ag-grid.com/javascript-grid-filter-provided-simple/
        if (gridParams.containsKey("filterModel")) {
            List<JSONObject> listSqlSearchVO = new ArrayList<>();
            Set<String> filterFields = gridParams.getJSONObject("filterModel").keySet();
            for (String field : filterFields) {
                JSONObject filterObj = gridParams.getJSONObject("filterModel").getJSONObject(field);
                String filterType = filterObj.getString("filterType");
                // 多个条件 operator, condition1, condition2
                if (filterObj.containsKey("operator")
                        && filterObj.containsKey("condition1")
                        && filterObj.containsKey("condition2")) {
                    String operator = filterObj.getString("operator");

                    JSONObject condition1 = filterObj.getJSONObject("condition1");
                    JSONObject sqlSearchVo1 = covertSqlSearchVO(field, filterType, condition1);
                    sqlSearchVo1.put("bracket", "(");
                    listSqlSearchVO.add(sqlSearchVo1);

                    JSONObject condition2 = filterObj.getJSONObject("condition2");
                    JSONObject sqlSearchVo2 = covertSqlSearchVO(field, filterType, condition2);
                    sqlSearchVo2.put("bracket", ")");
                    if ("OR".equalsIgnoreCase(operator)) {
                        sqlSearchVo2.put("andOr", "or");
                    }
                    listSqlSearchVO.add(sqlSearchVo2);
                } else {
                    JSONObject sqlSearchVo = covertSqlSearchVO(field, filterType, filterObj);
                    listSqlSearchVO.add(sqlSearchVo);
                }
            }
            if (listSqlSearchVO != null) {
                JSONArray queryData = queryDataObject.getJSONArray("queryData");
                if (queryData == null) {
                    queryData = new JSONArray();
                }
                for (JSONObject searchVo : listSqlSearchVO) {
                    queryData.add(searchVo);
                }
                queryDataObject.put("queryData", queryData);
            }
            gridParams.remove("filterModel");
        }
    }

    private JSONObject covertSqlSearchVO(String field, String filterType, JSONObject conditionObj) {
        JSONObject sqlSearchVo = new JSONObject();
        sqlSearchVo.put("name", field);
        sqlSearchVo.put("type", filterType);
        // type-过滤类型
        String type = conditionObj.getString("type");
        String filter = conditionObj.getString("filter");
        // sql注入检查
        if (StringUtil.isNotEmpty(filter)) {
            filter = filter.replaceAll("'", "");
        }
        sqlSearchVo.put("con", ViewUtil.convertCon(type));
        sqlSearchVo.put("val", filter);
        if ("date".equalsIgnoreCase(filterType)) {
            //inRange,dateFrom,dateTo
            if (conditionObj.containsKey("dateFrom")) {
                // FYQ 移除时分秒
                java.util.Date date = DateUtil.parse(conditionObj.getString("dateFrom"));
                String formatDate = DateUtil.formatDate(date);
                sqlSearchVo.put("val", formatDate);
                // sqlSearchVo.put("val", conditionObj.getString("dateFrom"));
            }
            if (conditionObj.containsKey("dateTo")) {
                java.util.Date date = DateUtil.parse(conditionObj.getString("dateTo"));
                String formatDate = DateUtil.formatDate(date);
                sqlSearchVo.put("val2", formatDate);
                // sqlSearchVo.put("val2", conditionObj.getString("dateTo"));
            }
        } else if ("number".equalsIgnoreCase(filterType)) {
            //inRange,filter,filterTo
            if (conditionObj.containsKey("filterTo")) {
                sqlSearchVo.put("val2", conditionObj.getString("filterTo"));
            }
        } else if ("text".equalsIgnoreCase(filterType)) {
            //filter
        } else if ("set".equalsIgnoreCase(filterType) || "dict".equalsIgnoreCase(filterType) || "map".equalsIgnoreCase(filterType)) {
            //filter
            sqlSearchVo.put("con", "in");
        }
        return sqlSearchVo;
    }


    public ResultMsg<String> exportData(SqlViewRequest request, HttpServletResponse response) {
        String sheetName = "";

        String code = request.getCode();

        try {
            this.logger.info("自定义列表-数据导出,code:{}", code);

            FormCustSql vo = this.formCustSqlManager.getByCode(code, true);

            List<FormCustSqlFields> settings = ViewUtil.getFields(vo.getFieldsList(), "5");
            if (CollectionUtils.isEmpty(settings)) {
                this.logger.error("找不到可以导出的字段列表,code:{}", code);
                return ResultMsg.ERROR("找不到可以导出的字段列表");
            }
            String fieldColumns = request.getFieldColumns();
            if (StringUtils.isNotEmpty(fieldColumns)) {
                String[] fieldColumnsArray = fieldColumns.split(",");
                List<String> fieldSet = Arrays.asList(fieldColumnsArray);
                Iterator<FormCustSqlFields> iterator = settings.iterator();
                while (iterator.hasNext()) {
                    FormCustSqlFields tmSqlFieldSettingVO = iterator.next();
                    if (!fieldSet.contains(tmSqlFieldSettingVO.getFieldName())) {
                        iterator.remove();
                    }
                }
            }

            sheetName = vo.getName();

            setResponseContext(response, sheetName);

            ServletOutputStream servletOutputStream = response.getOutputStream();


            WritableWorkbook wb = Workbook.createWorkbook(servletOutputStream);


            WritableSheet sheet = wb.createSheet(sheetName, 0);


            setSheetHeader(sheetName, settings, sheet);


            setSheetDataList(sheet, settings, request);

            wb.write();
            wb.close();
            servletOutputStream.flush();
            servletOutputStream.close();
            this.logger.info("导出" + sheetName + "列表成功  request=[{}]", request);


            return null;
        } catch (Exception e) {
            this.logger.error("导出" + sheetName + "列表失败 request=[{}] , e : {}", request, e);
            return ResultMsg.ERROR("导出异常:{}" + ExceptionUtil.getRootErrorMseeage(e));
        }
    }


    private void setSheetDataList(WritableSheet sheet, List<FormCustSqlFields> settings, SqlViewRequest request) throws Exception {
        // TODO 配置环境变量设置导出数据最大值
        // The maximum number of rows permitted on a worksheet been exceeded 65536
        int maxRow = 20000;

        request.setPageSize(maxRow);

        ResultMsg<SqlViewDataVO> data = listData(request);

        SqlViewDataVO sqlViewDataVO = data.getData();

        JSONArray list = (JSONArray) sqlViewDataVO.getList();

        int col = 0;
        int row = 2;
        int count = 1;


        Map<String, JSONObject> dicMap = new HashMap<>();  //共享数据字典
        String dateFormat = "yyyy-MM-dd hh:mm:ss";
        jxl.write.DateFormat df = new jxl.write.DateFormat(dateFormat);
        WritableCellFormat wcf = new jxl.write.WritableCellFormat(df);
        Map<String, WritableCellFormat> dateFormatMap = new HashMap<>();
        dateFormatMap.put(dateFormat, wcf);
        for (int i = 0; i < list.size(); i++) {
            JSONObject jo = (JSONObject) list.get(i);
            sheet.addCell(new Label(col++, row, "" + count++));


            for (FormCustSqlFields setting : settings) {
                String fieldName = setting.getFieldName();
                String val = jo.getString(fieldName);
                String format = setting.getDateFormat();
                int controlType = setting.getControlType().intValue();


                JSONObject formatObj = getFormatObj(controlType, format, dicMap);

                // dataType: datetime/number/varchar

                val = getValue(controlType, val, format, formatObj);

                //特殊的百分比格式化 直接数字去掉小数点后的多余的0 + %
                if (StringUtils.isNotBlank(val) && "percentFormat".equals(format) && NumberUtil.isNumber(val)) {
                    val = NumberUtil.toStr(NumberUtil.toBigDecimal(val)) + "%";
                }

                //是否需要转数字
                boolean toNumber = false;
                if ("number".equals(setting.getDataType()) && NumberUtil.isNumber(val)) {
                    Class t = jo.get(fieldName).getClass();
                    //Long 类型的一般为id 不转换为数字，不然显示的科学计算法
                    if (t.isAssignableFrom(Integer.class) || t.isAssignableFrom(Double.class) || t.isAssignableFrom(BigDecimal.class)) {
                        toNumber = true;
                    }
                }
                if (toNumber) {
/*                    NumberFormat nf = new NumberFormat("#.##");    //设置数字格式
                    WritableCellFormat wcfN = new WritableCellFormat(nf); //设置表单格式
                    Number labelNF = new Number(col++, row, NumberUtil.toBigDecimal(val).doubleValue(), wcfN); //格式化数值
                    sheet.addCell(labelNF);*/
                    //设置为数值 excel会自动去除小数位.00,这里统一转为double
                    sheet.addCell(new Number(col++, row, NumberUtil.toBigDecimal(val).doubleValue()));
                } else if ("datetime".equals(setting.getDataType()) && StringUtil.isNotEmpty(val)) {
                    // fix ++ try/catch 重复调用  0816
                    try {
                        // fix 4491日期根据dateformat格式显示
                        dateFormat = "yyyy-MM-dd hh:mm:ss";
                        if (StringUtils.isNotBlank(setting.getDateFormat())) {
                            dateFormat = setting.getDateFormat();
                        }
                        if (!dateFormatMap.containsKey(dateFormat)) {
                            df = new jxl.write.DateFormat(dateFormat);
                            wcf = new jxl.write.WritableCellFormat(df);
                            dateFormatMap.put(dateFormat, wcf);
                        }
                        Calendar c = Calendar.getInstance();
                        c.setTime(DateUtil.parse(val));
                        WritableCell cell = new DateTime(col, row, c.getTime(), dateFormatMap.get(dateFormat));
                        sheet.addCell(cell);
                    } catch (Throwable t) {
                        t.printStackTrace();
                        logger.debug("cell datetime is validate :" + val + ", message:" + t.getMessage());
                        sheet.addCell(new Label(col, row, val));
                    }
                    col++;
                } else {
                    sheet.addCell(new Label(col++, row, val));
                }

            }

            row++;
            col = 0;
            if (row % 1000 == 2) {
                logger.debug("row={}", row);
            }
        }
    }


    private String getValue(int controlType, String val, String format, JSONObject formatObj) {
        Date date;
        switch (controlType) {
            case 4:  //下拉
            case 5:  //字典
            case 6:  //单选
            case 7:  //字典单选
                if (formatObj.containsKey(val)) {
                    val = formatObj.getString(val);
                } else {
                    val = "";
                }
                break;
            case 8:  //复选
            case 9:  //字典（复选）
                if (StringUtil.isNotEmpty(val)) {
                    String[] valArray = val.split(",");
                    List<String> valList = new ArrayList<>();
                    for (String tempVal : valArray) {
                        if (formatObj.containsKey(tempVal)) {
                            val = formatObj.getString(tempVal);
                            valList.add(val);
                        }
                    }
                    val = ArrayUtil.join(valList, ",");
                }
                break;
            case 3:  //日期
                date = null;
                if (!StringUtils.isEmpty(val) && val.indexOf("{") >= 0) {
                    formatObj = JSONObject.parseObject(val);
                    date = new Date(formatObj.getLong("time").longValue());
                } else if (StringUtils.isNotEmpty(val)) {
                    if (StringUtils.isNumeric(val)) {
                        date = new Date(Long.parseLong(val));
                    } else {
                        try {
                            date = DateUtils.parseDate(val);
                        } catch (Exception e) {
                            // error
                        }

                    }
                }
                if (BeanUtils.isNotEmpty(date)) {
                    if (StringUtils.isEmpty(format)) {
                        format = "yyyy-MM-dd HH:mm:ss";
                    } else {
                        format = format.replaceAll("hh", "HH");
                    }
                    String dateStr = DateUtils.formatDate(date, format);
                    val = dateStr;
                    break;
                }
                val = "";
                break;
            case 10: //json
            case 20: //颜色格式化
                if (StringUtil.isNotEmpty(val)) {
                    //val = JSONObjectUtil.getValByKey(val, formatObj);
                    val = formatObj.getString(val);
                } else {
                    val = "";
                }
                break;
            case 18: //后台字典
                if (val == null){
                    val = "";
                    break;
                }
                if (val.contains(",")){
                    String[] vv = val.split(",");
                    List<String> vvs = new ArrayList<>();
                    for (String v : vv){
                        if (formatObj.containsKey(v)) {
                            vvs.add( formatObj.getString(v) );
                        }
                    }
                    if (vvs.isEmpty()){
                        val = "";
                    }else{
                        val = String.join(",", vvs);
                    }
                }else{
                    if (formatObj.containsKey(val)) {
                        val = formatObj.getString(val);
                    } else {
                        val = "";
                    }
                }

                break;
            case 21: //节点
                break;
            case 22: //主表流程
                //val = "";
                if (StringUtils.isNotEmpty(val)) {
                    List<BpmTask> tasks = bpmTaskManager.getByInstId(val);
                    val = tasks.stream().map(t -> t.getName()).collect(Collectors.joining(","));
                    if (StringUtils.isBlank(val)) {
                        val = "已经结束";
                    }
                }
                break;
        }
        return val;
    }

    private JSONObject getFormatObj(int controlType, String format, Map<String, JSONObject> dicMap) {
        JSONObject tempJo = new JSONObject();


        // 4-下拉 6-单选 8-复选, 10-json
        if (Util.list((Object[]) new Integer[]{Integer.valueOf(4), Integer.valueOf(6), Integer.valueOf(8), Integer.valueOf(10),
        }).contains(Integer.valueOf(controlType))) {
            tempJo = JSONObject.parseObject(format);
            return tempJo;
        }
        // 20-颜色格式化
        if (20 == Integer.valueOf(controlType) && StringUtil.isNotEmpty(format)) {
            //key-label-style
            String[] dataItems = format.split(",");
            for (String strItem : dataItems) {
                String[] labels = strItem.split("-");
                if (labels.length >= 2) {
                    tempJo.put(labels[0], labels[1]);
                }
            }
            return tempJo;
        }

        // 数据字典 5-字典 7-字典单选 9-字典复选 18-字典-后台
        if (Util.list((Object[]) new Integer[]{Integer.valueOf(5), Integer.valueOf(7), Integer.valueOf(9), Integer.valueOf(18)
        }).contains(Integer.valueOf(controlType))) {

            if (!dicMap.containsKey(format)) {
                List<DataDictDTO> dictNodeList = this.dataDictService.getDictNodeList4grid(format);   //.getDictNodeList(format, Boolean.valueOf(false));
                JSONObject dicJo = new JSONObject();
                dicMap.put(format, dicJo);
                if (!CollectionUtils.isEmpty(dictNodeList)) {
                    for (DataDictDTO dic : dictNodeList) {
                        dicJo.put(dic.getKey(), dic.getName());
                    }
                }
            }
            tempJo = dicMap.get(format);
        }

        return tempJo;
    }


    private void setSheetHeader(String sheetName, List<FormCustSqlFields> settings, WritableSheet sheet) throws WriteException {
        sheet.mergeCells(0, 0, settings.size(), 0);


        WritableCellFormat writableCellFormat = JxlUtils.createTitleFormat();
        CellFormat columnFormat = JxlUtils.createColumnFormat();
        sheet.addCell(new Label(0, 0, sheetName, writableCellFormat));
        int col = 0;
        int row = 1;


        sheet.addCell(new Label(col++, row, "序号", columnFormat));


        for (FormCustSqlFields setting : settings) {
            sheet.addCell(new Label(col++, row, setting.getFieldDesc(), columnFormat));
        }
    }


    private void setResponseContext(HttpServletResponse response, String sheetName) throws UnsupportedEncodingException {
        sheetName = new String(sheetName.getBytes(StandardCharsets.UTF_8), "ISO8859-1");
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + sheetName + ".xls");
    }


    public ResultMsg<String> changeData(String code, String buttonAlias, String ids) {
        FormCustSql vo = this.formCustSqlManager.getByCode(code, true);

        String buttons = vo.getButtons();
        if (StringUtil.isEmpty(buttons)) {
            this.logger.error("自定义按钮不存在,code:{},btnAlias:{}", code, buttonAlias);
            return ResultMsg.ERROR("自定义按钮不存在");
        }

        List<SqlButtonVO> buttonList = JSONObjectUtil.toList(buttons, SqlButtonVO.class);

        SqlButtonVO btnButtonVO = null;
        for (SqlButtonVO sqlButtonVO : buttonList) {
            if (Util.equals(sqlButtonVO.getAlias(), buttonAlias) &&
                    Util.equals(sqlButtonVO.getClickType(), Integer.valueOf(1))) {
                btnButtonVO = sqlButtonVO;
            }
        }

        if (btnButtonVO == null) {
            this.logger.error("自定义按钮不存在,code:{},btnAlias:{}", code, buttonAlias);
            return ResultMsg.ERROR("自定义按钮不存在");
        }


        String dataChangeStr = btnButtonVO.getDataChange();
        if (StringUtil.isEmpty(dataChangeStr)) {
            this.logger.error("自定义按钮数值变化字段为空,code:{},btnAlias:{}", code, buttonAlias);
            return ResultMsg.ERROR("自定义按钮数值变化字段为空");
        }


        String tableName = vo.getTableName();
        List<FormCustSqlFields> fieldsList = vo.getFieldsList();


        Map<String, String> fieldNameColumnMap = fieldsList.stream().filter(fields -> Util.equals(fields.getTableName(), tableName)).collect(Collectors.toMap(FormCustSqlFields::getFieldName, FormCustSqlFields::getName));

        List<String> columnList = fieldsList.stream().map(FormCustSqlFields::getName).collect(Collectors.toList());

        String dataChangeSql = "";
        String[] dataChangeArray = dataChangeStr.split(";");
        List<SqlButtonDataChangeVO> dataChangeList = new ArrayList<>();
        for (String dataChange : dataChangeArray) {
            String[] changeData = dataChange.split("=");
            if (changeData.length != 2) {
                this.logger.error("自定义按钮数值变化配置不正确,code:{},btnAlias:{},dataChange:{}", code, buttonAlias, dataChange);
                return ResultMsg.ERROR("自定义按钮数值变化配置不正确:" + dataChange);
            }
            SqlButtonDataChangeVO dataChangeVO = new SqlButtonDataChangeVO();
            String fieldName = changeData[0];
            String value = changeData[1];
            dataChangeVO.setFieldName(fieldName);
            dataChangeVO.setValue(value);

            if (columnList.contains(fieldName)) {
                dataChangeVO.setColumn(fieldName);
            } else if (fieldNameColumnMap.containsKey(fieldName)) {
                String column = fieldNameColumnMap.get(dataChangeVO.getFieldName());
                dataChangeVO.setColumn(column);
            } else {
                this.logger.error("自定义按钮数值变化配置不正确,主表中找不到对应的字段,code:{},btnAlias:{},dataChange:{},column:{}", code, buttonAlias, dataChange, fieldName);

                return ResultMsg.ERROR("自定义按钮数值变化配置不正确,主表中找不到对应的字段:" + dataChange);
            }
            dataChangeSql = dataChangeSql + dataChangeVO.getColumn() + " = " + dataChangeVO.getValue() + ",";
            dataChangeList.add(dataChangeVO);
        }

        ids = ArrayUtil.join(ids.split(","), "','");

        dataChangeSql = dataChangeSql.substring(0, dataChangeSql.length() - 1);


        String pkName = fieldNameColumnMap.get(vo.getPkName());

        String updateSql = "update " + tableName + " set " + dataChangeSql + " where " + pkName + " in (" + ids + ")";

        String dsKey = vo.getDsKey();

        JdbcTemplate jdbcTemplate = JdbcTemplateUtil.getJdbcTemplate(dsKey);

        jdbcTemplate.execute(updateSql);

        return ResultMsg.SUCCESS("操作成功");
    }
}
